//
//  DataAccess.m
//  RealatorView
//
//  Created by Ricky Jones on 1/20/09.
//  Copyright 2009 Vaporsoftware. All rights reserved.
//

#import "DataAccess.h"
#import "LocationImage.h"
#import "RealatorViewAppDelegate.h"

@implementation DataAccess



	-(NSString*) GetDatabaseLocation
	{
		
		RealatorViewAppDelegate *appDelegate = (RealatorViewAppDelegate *)[[UIApplication sharedApplication] delegate];
		return [appDelegate getDBPath];
		
	}



	-(void)DeleteLocation: (int) locationID
	{
		NSString* sql = @"delete from Location where ID = $ID";
		DBConnection * connection = [[DBConnection alloc] initWithPath:[self GetDatabaseLocation]];
		DBStatement* statment = [[DBStatement alloc] initWithConnection: connection SQL:sql];
		[statment setValue:[NSNumber numberWithInt:locationID] forKey:@"$ID"];
		[statment execute];
	}


	-(void)AddLocation: (Location*) locationToAdd
	{

		NSString* sql = @"insert into Location (Name,Address1,Address2,City,State,Zip,Latitude,Longitude) values ($Name,$Address1,$Address2,$City,$State,$Zip,$lat,$long)";
		DBConnection *connection = [[DBConnection alloc] initWithPath:[self GetDatabaseLocation]];
		DBStatement* statment = [[DBStatement alloc] initWithConnection: connection	SQL:sql];
		[statment setValue:[locationToAdd Name] forKey:@"$Name"];
		[statment setValue:[locationToAdd Address1] forKey:@"$Address1"];
		[statment setValue:[locationToAdd Address2] forKey:@"$Address2"];
		[statment setValue:[locationToAdd City] forKey:@"$City"];
		[statment setValue:[locationToAdd State] forKey:@"$State"];
		[statment setValue:[locationToAdd Zip] forKey:@"$Zip"];
		[statment setValue:[NSNumber numberWithDouble:[locationToAdd myLocation].latitude] forKey:@"$lat"];
		[statment setValue:[NSNumber numberWithDouble:[locationToAdd myLocation].longitude] forKey:@"$long"];		
		[statment execute];
		
		[self StoreImages:[[connection lastInsertRowid] intValue] :[locationToAdd Images]];
	}


	-(void)UpdateLocation: (Location*) locationToUpdate
	{
		NSString* sql = @"update Location set Name = $Name,Address1 = $Address1,Address2 = $Address2,City = $City, State = $State, Zip = $Zip,Latitude = $lat,Longitude = $long where ID = $locationID";
		DBConnection* connection = [[DBConnection alloc] initWithPath:[self GetDatabaseLocation]];
		DBStatement* statment = [[DBStatement alloc] initWithConnection: connection SQL:sql];
		[statment setValue:[locationToUpdate Name] forKey:@"$Name"];
		[statment setValue:[locationToUpdate Address1] forKey:@"$Address1"];
		[statment setValue:[locationToUpdate Address2] forKey:@"$Address2"];
		[statment setValue:[locationToUpdate City] forKey:@"$City"];
		[statment setValue:[locationToUpdate State] forKey:@"$State"];
		[statment setValue:[locationToUpdate Zip] forKey:@"$Zip"];
		[statment setValue:[NSNumber numberWithDouble:[locationToUpdate myLocation].latitude] forKey:@"$lat"];
		[statment setValue:[NSNumber numberWithDouble:[locationToUpdate myLocation].longitude] forKey:@"$long"];
		[statment execute];
		[self DeleteImagesForLocation:[locationToUpdate LocationID]];
		[self StoreImages:[locationToUpdate LocationID] :[locationToUpdate Images]];
	}






	-(Location*) GetLocation:(int)locationID
	{
		NSString* sql = @"select * from location where id = $id";
		DBConnection* connection = [[DBConnection alloc] initWithPath:[self GetDatabaseLocation]];
		DBStatement* statment = [[DBStatement alloc] initWithConnection:connection SQL:sql];
		[statment setValue:[NSNumber numberWithInt:locationID] forKey:@"$id"];
		
		Location * locationToReturn = [[Location alloc] init];
		for(id row in statment)
		{
			locationToReturn = [self GetLocationFromRow: row :YES];
		}
		return locationToReturn;
	
	}


	-(Location*) GetLocationFromRow:(id) row : (BOOL) includeImage
	{
		Location* a = [[Location alloc] init];
		[a setLocationID:[[row valueForKey:@"ID"] intValue]];
		[a setName:[row valueForKey:@"Name"]];
		[a setAddress1:[row valueForKey:@"Address1"]];
		[a setAddress2:[row valueForKey:@"Address2"]];
		[a setCity:[row valueForKey:@"City"]];
		[a setState:[row valueForKey:@"State"]];
		[a setZip:[row valueForKey:@"Zip"]];
		CLLocationCoordinate2D cord;
		cord.latitude = [[row valueForKey:@"Latitude"] doubleValue];
		cord.longitude = [[row valueForKey:@"Latitude"] doubleValue];
						    
		[a setMyLocation:cord];

		if(includeImage)
		{
			[a setImages:[self GetImages:[a LocationID]]];
		}
		return a;
	}

	-(NSMutableArray*) GetLocations:(NSString*) area
	{
	
		NSString * sql = @"select * from Location";
		DBConnection *connection = [[DBConnection alloc] initWithPath:[self GetDatabaseLocation]];
		DBStatement* statment = [[DBStatement alloc] initWithConnection: connection	SQL:sql];
		NSMutableArray* returnArray = [[NSMutableArray alloc] init];
		for(id locationRow in statment)
		{
			Location* newLocation = [self GetLocationFromRow: locationRow :NO];
			[returnArray addObject:newLocation];
		}
		return returnArray;
	}
	


	-(NSMutableArray *) GetImages:(int) locationID
	{
		NSString* sql = @"select * from LocationImage where locationID = $value";
		DBConnection *connection = [[DBConnection alloc] initWithPath:[self GetDatabaseLocation]];
		DBStatement* statment = [[DBStatement alloc] initWithConnection: connection	SQL:sql];
		[statment setValue:[NSNumber numberWithInt:locationID] forKey:@"$value"];
		NSMutableArray* images = [[NSMutableArray alloc] init];
		for(id row in statment)
		{
			NSData* imageData = [[row valueForKey:@"Data"] dataValue];
			LocationImage* newImage = [[LocationImage alloc] initWithData:imageData];
			[newImage setLocationID:[[row valueForKey:@"LocationID"] intValue]];
			[newImage setImageID: [[row valueForKey: @"ID"] intValue]];
			[newImage setX: [[row valueForKey:@"X"] intValue]];
			[newImage setY: [[row valueForKey: @"Y"] intValue]];
			[images addObject:newImage];
		}
		return images;
	}

	-(void) DeleteImagesForLocation: (int) locationID
	{
		NSString *sql = @"delete from LocationImage where LocationID = $locationID";
		DBConnection* connection = [[DBConnection alloc] initWithPath:[self GetDatabaseLocation]];
		DBStatement* statment = [[DBStatement alloc] initWithConnection:connection SQL:sql];
		[statment setValue:[NSNumber numberWithInt:locationID] forKey:@"$locationID"];
		[statment execute];
		
	}
	-(void) StoreImages:(int)locationID: (NSMutableArray*)imageToStore
	{
		NSString* sql = @"insert into LocationImage(Data,LocationID,X,Y) values ($data,$locationID,$x,$y)";
		DBConnection* connection = [[DBConnection alloc] initWithPath:[self GetDatabaseLocation]];
		DBStatement* statment = [[DBStatement alloc] initWithConnection:connection SQL:sql];
		for(id image in imageToStore)
		{
			NSData *imageData = UIImagePNGRepresentation(image);

			[statment setValue:imageData forKey:@"$data"];
//			[statment setValue:[NSNumber numberWithInt:locationID] forKey:@"$ID"];
			[statment setValue:[NSNumber numberWithInt:locationID] forKey:@"$locationID"];
			[statment setValue:[NSNumber numberWithInt:[image X]] forKey:@"$x"];
			[statment setValue:[NSNumber numberWithInt:[image Y]] forKey:@"$y"];
			[statment execute];
		}
	
	}


@end
